package edu.byu.intex2.data.dao;

import edu.byu.intex2.data.Cache;
import edu.byu.intex2.data.ConnectionPool;
import edu.byu.intex2.data.DataException;
import edu.byu.intex2.data.GUID;
import edu.byu.intex2.data.bo.Part;
import edu.byu.intex2.data.bo.PartsAtServiceFacility;
import edu.byu.intex2.data.bo.ServiceFacility;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * PartsAtServiceFacility Data Access Object
 * @author Brady
 */
public class PartsAtServiceFacilityDAO {

    /////////////////////////////////////////////
    ///   Singleton code
    private static PartsAtServiceFacilityDAO instance = null;

    /** Creates a new instance of PartsAtServiceFacilityDAO */
    private PartsAtServiceFacilityDAO() {
    }//constructor

    /** Retrieves the single instance of this class */
    public static synchronized PartsAtServiceFacilityDAO getInstance() {
        if (instance == null) {
            instance = new PartsAtServiceFacilityDAO();
        }
        return instance;
    }//getInstance

    ////////////////////////////////////////////
    ///   CREATE methods
    /**
     * Creates a new partsAtServiceFacility in the database
     * @param id GUID
     * @param partId id of part
     */
    public PartsAtServiceFacility create(ServiceFacility sf, Part part) throws DataException {
        String id = sf.getId() + part.getId();
        PartsAtServiceFacility partsAtServiceFacility = new PartsAtServiceFacility(id); // combined guid
        partsAtServiceFacility.setObjectAlreadyInDB(false);

        // Set ServiceFacility ID
        partsAtServiceFacility.setServiceFacility(sf);

        // Set Part
        partsAtServiceFacility.setPart(part);

        Cache.getInstance().put(partsAtServiceFacility.getId(), partsAtServiceFacility); // put in cache just in case...
        return partsAtServiceFacility;
    }//create


    ////////////////////////////////////////////
    ///   READ methods
    /**
     * Reads an existing partsAtServiceFacility from the database     */
    public PartsAtServiceFacility read(String serviceFacilityId, String partId) throws DataException {
        String id = serviceFacilityId + partId;
        // Check Cache
        if (Cache.getInstance().containsKey(id)) {
            return (PartsAtServiceFacility) Cache.getInstance().get(id);
        }

        // Get Connection
        Connection conn = ConnectionPool.getInstance().get();

        // Try Reading with connection
        try {
            return read(serviceFacilityId, partId, conn);
        } catch (Exception e) {
            throw new DataException("An error occurred while reading the PartsAtServiceFacility object information.", e);
        } finally {
            ConnectionPool.getInstance().release(conn);
        }
    }

    /**
     * Internal method to read an existing partsAtServiceFacility from the database
     * @return partsAtServiceFacility
     */
    synchronized PartsAtServiceFacility read(String serviceFacilityId, String partId, Connection conn) throws Exception {
        // Check Cache
        String id = serviceFacilityId + partId;
        if (Cache.getInstance().containsKey(id)) {
            return (PartsAtServiceFacility) Cache.getInstance().get(id);
        }

        // Prepare a SQL Statement
        PreparedStatement stmt = conn.prepareStatement("SELECT * FROM parts_at_service_facility where service_facility_id=? AND part_id=?");

        try {
            stmt.setString(1, serviceFacilityId);
            stmt.setString(2, partId);
            ResultSet rs = stmt.executeQuery(); // execute query
            if (rs.next()) {
                // Read the record with the result set
                return readRecord(rs, conn);
            }//if
            throw new DataException("PartsAtServiceFacility with serviceFacilityId '" + serviceFacilityId + "' not found.");
        } finally {
            stmt.close();
        }
    }//read

    /** Internal method to create a partsAtServiceFacility object from a record  */
    synchronized PartsAtServiceFacility readRecord(ResultSet rs, Connection conn) throws Exception {
        String id = rs.getString("service_facility_id") + rs.getString("part_id");
        if (Cache.getInstance().containsKey(id)) {
            return (PartsAtServiceFacility) Cache.getInstance().get(id);
        }
        // Create new partsAtServiceFacility object
        PartsAtServiceFacility partsAtServiceFacility = new PartsAtServiceFacility(id);

        // PartsAtServiceFacility is already in DB
        partsAtServiceFacility.setObjectAlreadyInDB(true);

        // Put partsAtServiceFacility in cache
        Cache.getInstance().put(partsAtServiceFacility.getId(), partsAtServiceFacility);

        // Set PartsAtServiceFacility Attributes
        partsAtServiceFacility.setServiceFacilityId(rs.getString("service_facility_id"));
        partsAtServiceFacility.setPartId(rs.getString("part_id"));
        partsAtServiceFacility.setQuantity(rs.getInt("quantity_on_hand"));
        partsAtServiceFacility.setReorderLevel(rs.getInt("reorder_level"));
        partsAtServiceFacility.setRepurchaseQuantity(rs.getInt("repurchase_qty"));
        partsAtServiceFacility.setQuantityOnOrder(rs.getInt("qty_on_order"));

        // PartsAtServiceFacility is dirty and needs to be saved to DB
        partsAtServiceFacility.setDirty(false);

        return partsAtServiceFacility;
    }//readRecord


    /////////////////////////////////////////////
    ///   UPDATE methods
    /** Saves an existing partsAtServiceFacility in the database */
    public void save(PartsAtServiceFacility partsAtServiceFacility) throws DataException {
        // get a connection from the pool, save all of the serviceFacilitys first using the other partsAtServiceFacility method and then commit them in the database
        Connection conn = ConnectionPool.getInstance().get();

        try {
            save(partsAtServiceFacility, conn); // save with connection
            conn.commit();  // commit the db transaction
        } catch (Exception e) {
            try {
                //rollback if there were any errors saving, catch any other errors you might have
                conn.rollback();
            } catch (SQLException e2) {
                throw new DataException("Could not roll back the partsAtServiceFacility database transaction!", e2);
            }
            throw new DataException("An error occurred while saving the partsAtServiceFacility object information.", e);
        } finally {
            // release the connection
            ConnectionPool.getInstance().release(conn);
        }
    }

    /** Internal method to update a partsAtServiceFacility in the database */
    void save(PartsAtServiceFacility partsAtServiceFacility, Connection conn) throws Exception {
        // put the partsAtServiceFacility object to cache
        Cache.getInstance().put(partsAtServiceFacility.getId(), partsAtServiceFacility);

        // decide whether to update or insert into the database
        if (partsAtServiceFacility.isObjectAlreadyInDB() && partsAtServiceFacility.isDirty()) {
            update(partsAtServiceFacility, conn);
        } else if (partsAtServiceFacility.isDirty()) {
            insert(partsAtServiceFacility, conn);
        }//if
    }

    /** External method to update or create a partsAtServiceFacility in the database without committing connection */
    public void saveNoCommit(PartsAtServiceFacility partsAtServiceFacility, Connection conn) throws Exception {
        // Get Already in Databsae
         Boolean alreadyInDB = partsAtServiceFacility.isObjectAlreadyInDB();

        // decide whether to update or insert into the database
        if (alreadyInDB) {
            update(partsAtServiceFacility, conn);
        } else {
            insert(partsAtServiceFacility, conn);
        }

        // Save No Commit our pretty part
//        PartDAO.getInstance().saveNoCommit(partsAtServiceFacility.getPart(), conn);s
    }

    /** Saves an existing partsAtServiceFacility to the database */
    private void update(PartsAtServiceFacility partsAtServiceFacility, Connection conn) throws Exception {
        // use a prepared statement to prevent sql injection
        PreparedStatement stmt = conn.prepareStatement("UPDATE parts_at_service_facility SET quantity_on_hand=?, reorder_level=?, repurchase_qty=?, " +
                                                            "qty_on_order=?  WHERE service_facility_id=? AND part_id=?");
        try {
            // Get PartsAtServiceFacility Attributes and add them to the statement
            stmt.setInt(1, partsAtServiceFacility.getQuantity());
            stmt.setInt(2, partsAtServiceFacility.getReorderLevel());
            stmt.setInt(3, partsAtServiceFacility.getRepurchaseQuantity());
            stmt.setInt(4, partsAtServiceFacility.getQuantityOnOrder());
            stmt.setString(5, partsAtServiceFacility.getServiceFacilityId());
            stmt.setString(6, partsAtServiceFacility.getPartId());
            stmt.execute();

            // PartsAtServiceFacility is clean!
            partsAtServiceFacility.setDirty(false);
        } finally {
            stmt.close();  // make sure to close the prepared statement
        }
    }

    /** Inserts a new partsAtServiceFacility into the database */
    private void insert(PartsAtServiceFacility partsAtServiceFacility, Connection conn) throws Exception {
        PreparedStatement stmt = conn.prepareStatement("INSERT INTO parts_at_service_facility (part_id, service_facility_id, quantity_on_hand, reorder_level," +
                                                         "repurchase_qty, qty_on_order) VALUES (?, ?, ?, ?, ?, ?)");
        try {
            // Set PartsAtServiceFacility Attributes
            stmt.setString(1, partsAtServiceFacility.getPartId());
            stmt.setString(2, partsAtServiceFacility.getServiceFacilityId());
            stmt.setInt(3, partsAtServiceFacility.getQuantity());
            stmt.setInt(4, partsAtServiceFacility.getReorderLevel());
            stmt.setInt(5, partsAtServiceFacility.getRepurchaseQuantity());
            stmt.setInt(6, partsAtServiceFacility.getQuantityOnOrder());
            stmt.execute();

            partsAtServiceFacility.setObjectAlreadyInDB(true); // now in DB
            partsAtServiceFacility.setDirty(false); // object is clean
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            stmt.close(); // close the prepared statement
        }
    }

    /////////////////////////////////////////////////
    ///   DELETE methods
    /** We do not support deleting of business objects in this application */
    public void delete(PartsAtServiceFacility partsAtServiceFacility) throws DataException {
        throw new RuntimeException("Nice try. The delete function is not supported in this application.");
    }

    ////////////////////////////////////////////////
    ///   SEARCH methods
    /** Internal method to search by certain fields */
    private List<PartsAtServiceFacility> search(PreparedStatement stmt, Connection conn) throws Exception {
        List<PartsAtServiceFacility> psfs = new ArrayList<PartsAtServiceFacility>();

        try {
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                psfs.add(readRecord(rs, conn));
            }
        } finally {
            stmt.close();
            ConnectionPool.getInstance().release(conn);
        }

        return psfs;
    }

    /** Get Reorder PSFs */
    public List<PartsAtServiceFacility> getReorderPSFs() throws Exception {
        //establish connection and prepared statement
        Connection conn = ConnectionPool.getInstance().get();
        PreparedStatement stmt = conn.prepareStatement("SELECT * FROM parts_at_service_facility " +
                                                       "WHERE quantity_on_hand + qty_on_order < reorder_level");

        // run stmt through the search method, return a list of customers matching any name or phone number
        return search(stmt, conn);
    }
}
